Date Parts

Custom Column Date Parts are used to add date-logic categorization of data based on existing date-time columns presented in the data model. Date parts allow users to virtually add new columns like year, month, quarter and week to a data model without needing to change the model or the data. By building these groupings, time-based analysis of data becomes significantly easier and smarter. By adding date-part columns, the engine effectively adds a new virtual column to the data model that represents a new way to query results. Converting a simple date into a year or month can only be done at the grain and cannot be solved effectively using semantic calculations.

Date Parts available include:

  • Date "Numbers": Year, Quarter Number, Month Number, Week Number, Day of Month, Hour, Minute
  • Date "Text" : Full Quarter, Full Month, Full Week, Month Name

Note: Custom Columns allow the user to add granular logic to an existing model. This functionality is not available on MS OLAP, Tabular and SAP BW cubes and models since this is not possible on a predefined model framework.

Calendar Periods

All logical options using Pyramid's point and click dat parts assume a standard calendar period. To create non calendar periods, there are several alternatives:

  • Use the time intelligence widget in model to build materialized columns into the database. It has advanced settings to change the year definitions.
  • Use the Calendar slice to use data parts in filtering (only). The Calender slice has advanced settings to change the year definitions.
  • Manually build a view into your database with your custom logic and read it into Pyramid like a normal table.

Custom Column Date Part Types

The different Date Part types is simply based on the date-logic applied to the existing date-time field in the data model

Note: If the source field only has dates, the hour and minute date parts will return zeros.

The grid below, which shows all the different date part fields based on the first raw data column "dateKey" - which does not have any time elements.

Numeric groupings

  • Year - extracts the year number from the date.
  • Quarter - extracts the quarter number from the date
  • Month - extracts the month number from the date
  • Week - extracts the week number from the date
  • Day - extracts the day of the month from the date

If there were time elements in the 'dateKey' field then:

  • Hour - extracts the hour number from the time
  • Minute - extracts the minute from the time

Text groupings

Text groupings are often more useful for visualizations because they contain a more descriptive level of detail for dates.

  • Quarter - extracts the quarter and the year from the date
  • Month - extracts the month and the year from the date
  • Week - extracts the week and the year from the date
  • Month Name - extracts the 3 character month name from the date (English only)

Adding Date Parts

Step 1

Using a SQL data source with date-time columns / attributes, right click on the date/ time hierarchy in the Dimensions panel. Go to Date Part in the context menu.

Step 2

Select the required date part from the sub-menu. Pyramid executes a SQL script to extract the given date part.

Step 3

The custom column is produced and appears under Custom Columns in the Dimensions panel; open the Custom Columns dimension.

Step 4

Add the custom column to the query.